use Rated
go
drop trigger TRG_INS_Review
go
create trigger TRG_INS_Review on Review for Insert
as
begin
	declare @ratedItemID int -- Item being rated
	declare @rating float  -- This rating
	declare @currentRating float -- The Item's current Rating
	declare @currentReviewCount int -- the count of reviews so far
	declare @currentRatingSum int -- the Sum of all the reviews so far
	select @ratedItemID = RatedItemID, @rating = Rating from inserted
	select @currentRating = rating from RatedItem where id=@ratedItemID
	select @currentReviewCount = count(*) from review where ratedItemID=@ratedItemID
	select @currentRatingSum = sum(rating) from review where ratedItemID=@ratedItemID
	select @currentRatingSum = @currentRatingSum + @rating
	select @currentReviewCount = @currentReviewCount
	update RatedItem set Rating =  cast((cast(@currentRatingSum as float)/ cast(@currentReviewCount as float)) as float) where ID = @ratedItemID
end


create trigger TRG_UPD_Review on Review for Update
as
begin
	declare @ratedItemID int -- Item being rated
	declare @rating float  -- This rating
	declare @ratingtobeU
	declare @currentRating float -- The Item's current Rating
	declare @currentReviewCount int -- the count of reviews so far
	declare @currentRatingSum int -- the Sum of all the reviews so far
	select @ratedItemID = RatedItemID, @rating = Rating from inserted
	select @currentRating = rating from RatedItem where id=@ratedItemID
	select @currentReviewCount = count(*) from review where ratedItemID=@ratedItemID
	select @currentRatingSum = sum(rating) from review where ratedItemID=@ratedItemID
	select @currentRatingSum = @currentRatingSum + @rating
	select @currentReviewCount = @currentReviewCount
	update RatedItem set Rating =  cast((cast(@currentRatingSum as float)/ cast(@currentReviewCount as float)) as float) where ID = @ratedItemID
end